import hashlib
import json
import os
import sqlite3

import PySide6
from PySide6 import QtCore
from PySide6.QtCore import QObject, QIODevice, QTextStream
from PySide6.QtSql import QSqlDatabase
from PySide6.QtWidgets import QMainWindow

from config.config import Config
from config.VarTable import VarTable


class Operation_Database(QObject):
    def __init__(self, parent=None, db_path=None):
        super(Operation_Database, self).__init__(parent)
        # 获取变量
        self.signal_operat = parent.signal_operat
        self.queue_logging = parent.queue_logging
        self.db_path = db_path      # 获取数据库地址

        # 创建变量
        self.table = None                       # 存储table的实例
        self.var_window = None                  # 存储var表实例
        # self.connect_database()

    def connect_database(self):
        """ 连接数据库并获取游标 """
        # 判断是否存在该数据库，如果不存在就创建一个新的
        if not os.path.exists(self.db_path):
            # 创建数据库
            config = Config(self, configFile=self.db_path)
            config.init_configFile()
            # 生成config默认数据
            file = QtCore.QFile(':/config_default.json')
            f = file.open(QIODevice.ReadOnly | QIODevice.Text)
            a = QTextStream(file).readAll()
            config_all = json.loads(a)
            self.conn = sqlite3.connect(self.db_path)
            self.cur = self.conn.cursor()
            self.write_config_json_all(config_all)

            # 提交
            self.conn.commit()
        else:
            self.conn = sqlite3.connect(self.db_path)
            self.cur = self.conn.cursor()
        # self.signal.emit(25, [None], [None])

    def reconnect_database(self, db_path):
        """ 重新连接数据库 """
        try:
            self.conn.commit()
            self.conn.close()
        except Exception as e:
            pass
        self.db_path = db_path
        self.connect_database()

    def reconnect_new_database(self, db_path):
        """ 创建新的数据库，如果文件夹下有同名数据库就先删除 """
        try:
            self.conn.commit()
            self.conn.close()
        except Exception as e:
            pass
        self.db_path = db_path
        if os.path.exists(db_path):
            os.remove(db_path)
        self.connect_database()

    def is_existing_db_path(self, path):
        # 检查路径是否存在
        if os.path.exists(path):
            # 检查路径是否是文件
            if os.path.isfile(path):
                # 检查路径的扩展名是否为 .db
                if os.path.splitext(path)[1] == '.db':
                    return True
        return False

    def read_table_info(self):
        """ 从数据库中获取table_info """
        table_info = {}
        column_list = ['key'] + list(Config.column_RW.keys()) + list(Config.column_RO.keys())
        for key in column_list:
            table_info[key] = []
        self.cur.execute("SELECT * FROM var order by addr")
        result = self.cur.fetchall()
        for i in range(len(result)):
            for j in range(len(column_list)):
                # 修改默认单位类型
                if j == column_list.index('var_unit'):
                    if result[i][j] is None:
                        table_info[column_list[j]].append('')
                    else:
                        table_info[column_list[j]].append(result[i][j])
                # 修改action_define的类型
                elif j == column_list.index('action_define'):
                    if result[i][j]:
                        action_define = json.loads(result[i][j])
                        if type(action_define) == dict:
                            table_info[column_list[j]].append(action_define)
                        elif type(action_define) == list:
                            action_dict = {}
                            for key, value in enumerate(action_define):
                                action_dict[str(key)] = value
                            table_info[column_list[j]].append(action_dict)
                        else:
                            table_info[column_list[j]].append('')
                    else:
                        table_info[column_list[j]].append('')
                # 修改默认当前值类型
                elif j == column_list.index('var_current'):
                    if result[i][j] is None:
                        table_info[column_list[j]].append(0)
                    else:
                        table_info[column_list[j]].append(result[i][j])
                else:
                    table_info[column_list[j]].append(result[i][j])
        return table_info

    def read_config_json_all(self):
        """ 从数据库中获取所有的config_json并组成一个大的字典 """
        self.cur.execute("SELECT * FROM config")
        result = self.cur.fetchall()
        config_all = {}
        for i in range(len(result)):
            config_all[result[i][0]] = json.loads(result[i][1])
        return config_all

    def write_config_json_all(self, config_all):
        """ 将全部的config写入数据库 """
        # # 以全部写入的方式写入数据库，这样不会判断数据库中有没有该name
        # request_list = []
        # for key, value in config_all.items():
        #     request_list.append((json.dumps(value, ensure_ascii=False), key))
        # self.cur.executemany("UPDATE setting SET json_str=? WHERE name=?", request_list)

        # 以判断的形式分别写入数据库，如果有就更新，没有就插入一条

        # 获取已有的name
        self.cur.execute("SELECT name FROM config")
        result = self.cur.fetchall()
        name_list = []
        for i in range(len(result)):
            name_list.append(result[i][0])
        # 处理config_all
        for key, value in config_all.items():
            if key in name_list:
                self.cur.execute("UPDATE config SET json_str=? WHERE name=?", (json.dumps(value, ensure_ascii=False), key))
            else:
                self.cur.execute(" INSERT INTO config VALUES (?,?)", (key, json.dumps(value, ensure_ascii=False)))
        self.conn.commit()

    def update_default_value(self, table_info):
        """ 更新缺省值 """
        for i in range(len(table_info['uniqueID'])):
            if table_info['isWritable'][i] == 1 and table_info['isEEPROM'][i] == 1:
                # print(table_info['var_current'][i], table_info['uniqueID'][i])
                self.cur.execute("UPDATE var SET var_default=? WHERE uniqueID=?", (table_info['var_current'][i], table_info['uniqueID'][i]))
        self.conn.commit()
        return True

    def update_default_value_one(self, uniqueID, value):
        self.cur.execute("UPDATE var SET var_default=? WHERE uniqueID=?", (value, uniqueID))
        self.conn.commit()
        self.cur.execute("SELECT var_default from var WHERE uniqueID=?", (uniqueID,))
        result = self.cur.fetchall()
        if result[0][0] == value:
            return True
        else:
            return False

    def export_header(self, pre_name, adr_name, file_name):
        """
            从数据库当中导出 数据并生成头文件
            pre_name : 预定义名称
            adr_name : 地址名
            file_name: 文件名
        """
        header_data = ''    # 字符串数据

        # 获取e2prom变量个数
        self.cur.execute("SELECT COUNT(*) FROM var WHERE isEEPROM = 1")
        e2prom_count = self.cur.fetchone()[0]
        # header_data += '#ifndef ' + file_name.upper() + '_H\n' + '#define ' + file_name.upper() + '_H\n\n'
        # header_data += '#define EEPROM_SAVE_NUM ' + str(count) + '       //需要存储的数量\n'

        # 获取变量的总个数
        self.cur.execute("SELECT COUNT(*) FROM var")
        addr_count = self.cur.fetchone()[0]

        # 获取数据
        self.cur.execute("SELECT var_type, var_comment, uniqueID, addr FROM var")
        results = self.cur.fetchall()

        # 获取每一列的长度
        name_len = len(pre_name) + max(len(sublst[2]) for sublst in results)
        value_len = len(adr_name) + 6 + len(str(addr_count))

        # 添加文件头
        header_data += '#ifndef ' + file_name.upper() + '_H\n' + '#define ' + file_name.upper() + '_H\n\n'
        header_data += '#define {0:<{3}} {1:<{4}} // {2}\n'.format('HOSTPARAS_HASH', self.get_hostconfig_hash(str), 'hostParas的name和addr的哈希值', str(name_len), str(value_len))
        header_data += '#define {0:<{3}} {1:<{4}} // {2}\n'.format('EEPROM_SAVE_NUM', str(e2prom_count), '需要存储的数量', str(name_len), str(value_len))
        # header_data += '#define EEPROM_SAVE_NUM ' + str(e2prom_count) + '       //需要存储的数量\n'

        # 追加数据
        for result in results:
            if result[0] in ['5', '3', '1', 'bit', 'enum', 'int']:
                header_data += '#define {0:<{3}} {1:<{4}} // {2}\n'.format(pre_name + result[2],
                                                                            adr_name + '.u32[{}]'.format(result[3]),
                                                                            result[1], str(name_len), str(value_len))
                # header_data += '#define ' + pre_name + result[2] + '\t' + adr_name + '.u32[' + str(
                #     result[3]) + ']\t// ' + result[1] + '\n'
            else:
                header_data += '#define {0:<{3}} {1:<{4}} // {2}\n'.format(pre_name + result[2],
                                                                            adr_name + '.f32[{}]'.format(result[3]),
                                                                            result[1], str(name_len), str(value_len))
                # header_data += '#define ' + pre_name + result[2] + '\t' + adr_name + '.f32[' + str(
                #     result[3]) + ']\t// ' + result[1] + '\n'

        # 添加尾巴
        header_data += '\n#endif\n'
        return header_data

    def get_hostconfig_hash(self, return_type=str):
        # 获取名字和地址数据
        # self.cur.execute("SELECT var_name, addr FROM var ORDER BY addr")
        self.cur.execute("SELECT COUNT(*) FROM var WHERE isEEPROM = 1")
        results = str(self.cur.fetchone())

        self.cur.execute("""
                            SELECT addr, case WHEN var_type='enum' THEN 'u'
                                              WHEN var_type='bit' THEN 'u'
                                              WHEN var_type='int' THEN 'u'
                                              WHEN var_type='float' THEN 'f' end, 
                                            uniqueID from var ORDER BY addr""")
        results += str(self.cur.fetchall())
        sha256_hash = hashlib.md5()
        sha256_hash.update(results.encode())
        hex_all = sha256_hash.hexdigest()
        if return_type == str:
            hex_list = ['0x' + hex_all[i:i + 4] for i in range(0, len(hex_all), 4)]
            hex_str = str(hex_list).replace('[', '{').replace(']', '}').replace("'", '')
            return hex_str
        elif return_type == list:
            int_list = [int(hex_all[i:i + 4], 16) for i in range(0, len(hex_all), 4)]
            return int_list
        else:
            pass

    def __del__(self):
        self.conn.commit()
        self.conn.close()


if __name__ == "__main__":
    operation_db = Operation_Database(db_path='abc.db')
    # operation_db.read_table_info()
    a = {'dock_tree': {'size': [256, 331]}, 'file': {'csv_path': 'E:/upper/上位机配置.csv', 'h5_path': 'E:/upper/9513.h5', 'h5_compression': 'None', 'header_path': None, 'header_prefix': '', 'header_name': 'g_hostParas'}, 'communication_info': {'port1': '1027', 'port2': '1028', 'IP': '192.168.1.11', 'SN': '255.255.255.0', 'GW': '192.168.1.1', 'password': '12341234'}, 'program_update': {'IP': '192.168.1.11', 'PORT': '1029', 'check1': True, 'check2': False, 'file_path1': 'E:/upper/cpu1.hex', 'file_path2': ''}}
    operation_db.write_config_json_all(a)

